import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import matplotlib.pyplot as plt
import os
import seaborn as sns

# 设置中文字体支持
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False    # 用来正常显示负号

# 模拟数据生成
def generate_sample_data():
    # 生成时间维度数据 (2024年全年)
    dates = pd.date_range(start='2024-01-01', end='2024-12-31', freq='D')
    time_dim = pd.DataFrame({
        'date': dates,
        'week': dates.isocalendar().week,
        'month': dates.month,
        'year': dates.year
    })

    # 生成保单数据 (1000条记录)
    policy = pd.DataFrame({
        'policy_id': range(1, 1001),
        'policy_date': np.random.choice(dates, 1000),
        'premium': np.random.uniform(1000, 50000, 1000),  # 总保费
        'ceded_premium': np.random.uniform(0, 20000, 1000),  # 分出保费
        'policy_type': np.random.choice(['property', 'life', 'health'], 1000, p=[0.7, 0.2, 0.1]),
        'region': np.random.choice(['North', 'South', 'East', 'West'], 1000)
    })

    # 准备金数据
    reserve = pd.DataFrame({
        'date': np.random.choice(dates, 800),
        'undue_claim_reserve': np.random.uniform(10000, 500000, 800),  # 未决赔款准备金
        'life_reserve': np.random.uniform(0, 300000, 800),  # 寿险责任准备金
        'health_reserve': np.random.uniform(0, 200000, 800),  # 健康险责任准备金
        'claim_paid': np.random.uniform(0, 100000, 800),  # 赔付支出
        'ceded_claim': np.random.uniform(0, 50000, 800)  # 摊回赔付支出
    })

    # 赔款数据
    claim = pd.DataFrame({
        'claim_date': np.random.choice(dates, 600),
        'paid_claim': np.random.uniform(1000, 50000, 600),  # 已付赔款
        'reported_reserve': np.random.uniform(0, 30000, 600),  # 已报告未决准备金
        'unreported_reserve': np.random.uniform(0, 20000, 600),  # 未报告未决准备金
        'earned_premium': np.random.uniform(5000, 100000, 600)  # 已赚保费
    })

    # 资本数据
    capital = pd.DataFrame({
        'date': np.random.choice(dates, 100),
        'insurance_liability': np.random.uniform(1e6, 5e6, 100),  # 保险负债
        'operating_capital': np.random.uniform(500000, 2e6, 100),  # 运营资金
        'equity': np.random.uniform(2e6, 8e6, 100),  # 所有者权益
        'liability_cost_rate': np.random.uniform(0.03, 0.08, 100),  # 负债成本率
        'capital_cost_rate': np.random.uniform(0.05, 0.10, 100),  # 资本成本率
        'equity_cost_rate': np.random.uniform(0.08, 0.15, 100)  # 权益成本率
    })

    return time_dim, policy, reserve, claim, capital


# 指标计算函数
def calculate_metrics(time_dim, policy, reserve, claim, capital, period='month'):
    """
    计算所有指标并按指定时间维度聚合
    period: week/month/year
    """
    # 合并时间维度
    reserve = reserve.merge(time_dim, left_on='date', right_on='date')
    claim = claim.merge(time_dim, left_on='claim_date', right_on='date')
    capital = capital.merge(time_dim, left_on='date', right_on='date')
    policy = policy.merge(time_dim, left_on='policy_date', right_on='date')

    # 按时间维度分组
    group_col = period

    # 1. 长期险退保率
    long_term_policy = policy[policy['policy_type'].isin(['life', 'health'])]
    long_term_group = long_term_policy.groupby(group_col).agg(
        total_premium=('premium', 'sum'),
        total_ceded=('ceded_premium', 'sum')
    ).reset_index()

    reserve_group = reserve.groupby(group_col).agg(
        life_reserve=('life_reserve', 'mean'),  # 期初准备金取平均值
        health_reserve=('health_reserve', 'mean')
    ).reset_index()

    long_term_metrics = long_term_group.merge(reserve_group, on=group_col)
    long_term_metrics['long_term_surrender_rate'] = (
                                                            (long_term_metrics['total_premium'] - long_term_metrics[
                                                                'total_ceded']) /
                                                            (long_term_metrics['life_reserve'] + long_term_metrics[
                                                                'health_reserve'])
                                                    ) * 100

    # 2. 未决赔款准备金与赔款支出比 - 修复此处错误
    # 先分别计算各个聚合值
    reserve_agg = reserve.groupby(group_col).agg(
        undue_claim_reserve_diff=('undue_claim_reserve', lambda x: x.iloc[-1] - x.iloc[0]),
        total_claim_paid=('claim_paid', 'sum'),
        total_ceded_claim=('ceded_claim', 'sum')
    ).reset_index()

    # 然后计算净赔款支出
    reserve_agg['net_claim_paid'] = reserve_agg['total_claim_paid'] - reserve_agg['total_ceded_claim']

    # 避免除以0
    reserve_agg['net_claim_paid'] = reserve_agg['net_claim_paid'].replace(0, np.nan)

    reserve_agg['reserve_claim_ratio'] = (
                                                 reserve_agg['undue_claim_reserve_diff'] /
                                                 reserve_agg['net_claim_paid']
                                         ) * 100

    # 3-5. 各类赔付率
    claim_metrics = claim.groupby(group_col).agg(
        paid_claim=('paid_claim', 'sum'),
        reported_reserve=('reported_reserve', 'sum'),
        unreported_reserve=('unreported_reserve', 'sum'),
        earned_premium=('earned_premium', 'sum')
    ).reset_index()

    # 避免除以0
    claim_metrics['earned_premium'] = claim_metrics['earned_premium'].replace(0, np.nan)

    claim_metrics['paid_loss_ratio'] = (
                                               claim_metrics['paid_claim'] /
                                               claim_metrics['earned_premium']
                                       ) * 100

    claim_metrics['reported_loss_ratio'] = (
                                                   (claim_metrics['paid_claim'] + claim_metrics['reported_reserve']) /
                                                   claim_metrics['earned_premium']
                                           ) * 100

    claim_metrics['total_loss_ratio'] = (
                                                (claim_metrics['paid_claim'] + claim_metrics['reported_reserve'] +
                                                 claim_metrics['unreported_reserve']) /
                                                claim_metrics['earned_premium']
                                        ) * 100

    # 6. 综合资本成本率
    capital_metrics = capital.groupby(group_col).agg(
        total_insurance_liability=('insurance_liability', 'sum'),
        total_operating_capital=('operating_capital', 'sum'),
        total_equity=('equity', 'sum'),
        avg_liability_cost=('liability_cost_rate', 'mean'),
        avg_capital_cost=('capital_cost_rate', 'mean'),
        avg_equity_cost=('equity_cost_rate', 'mean')
    ).reset_index()

    # 计算总资本和各项占比
    capital_metrics['total_capital'] = (
            capital_metrics['total_insurance_liability'] +
            capital_metrics['total_operating_capital'] +
            capital_metrics['total_equity']
    )

    capital_metrics['liability_ratio'] = capital_metrics['total_insurance_liability'] / capital_metrics['total_capital']
    capital_metrics['capital_ratio'] = capital_metrics['total_operating_capital'] / capital_metrics['total_capital']
    capital_metrics['equity_ratio'] = capital_metrics['total_equity'] / capital_metrics['total_capital']

    capital_metrics['comprehensive_capital_cost'] = (
                                                            capital_metrics['liability_ratio'] * capital_metrics[
                                                        'avg_liability_cost'] +
                                                            capital_metrics['capital_ratio'] * capital_metrics[
                                                                'avg_capital_cost'] +
                                                            capital_metrics['equity_ratio'] * capital_metrics[
                                                                'avg_equity_cost']
                                                    ) * 100

    # 合并所有指标
    metrics = long_term_metrics[[group_col, 'long_term_surrender_rate']]
    metrics = metrics.merge(reserve_agg[[group_col, 'reserve_claim_ratio']], on=group_col)
    metrics = metrics.merge(claim_metrics[[group_col, 'paid_loss_ratio', 'reported_loss_ratio', 'total_loss_ratio']],
                            on=group_col)
    metrics = metrics.merge(capital_metrics[[group_col, 'comprehensive_capital_cost']], on=group_col)

    return metrics


# 可视化函数 (修复中文显示问题)
def visualize_metrics(metrics, period_type, output_dir):
    """可视化指标并保存到指定目录"""
    # 创建子目录
    period_dir = os.path.join(output_dir, period_type)
    os.makedirs(period_dir, exist_ok=True)

    # 确定实际的时间维度列名（week/month/year）
    period_col = period_type.replace('ly', '')  # 将 'weekly' 转换为 'week'

    # 设置图表风格
    sns.set_theme(style="whitegrid")

    # 1. 长期险退保率
    plt.figure(figsize=(12, 6))
    plt.plot(metrics[period_col], metrics['long_term_surrender_rate'], 'o-', color='royalblue')
    plt.title(f'长期险退保率 ({period_type}趋势)')
    plt.ylabel('退保率(%)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(os.path.join(period_dir, 'long_term_surrender_rate.png'), dpi=300)  # 提高分辨率
    plt.close()

    # 2. 未决赔款准备金与赔款支出比
    plt.figure(figsize=(12, 6))
    plt.bar(metrics[period_col], metrics['reserve_claim_ratio'], color='mediumseagreen')
    plt.title(f'未决赔款准备金与赔款支出比 ({period_type}趋势)')
    plt.ylabel('比例(%)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(os.path.join(period_dir, 'reserve_claim_ratio.png'), dpi=300)
    plt.close()

    # 3. 赔付率对比
    plt.figure(figsize=(12, 6))
    plt.plot(metrics[period_col], metrics['paid_loss_ratio'], 's-', label='已付赔付率')
    plt.plot(metrics[period_col], metrics['reported_loss_ratio'], 'o-', label='已报告赔付率')
    plt.plot(metrics[period_col], metrics['total_loss_ratio'], 'd-', label='总赔付率')
    plt.title(f'赔付率对比 ({period_type}趋势)')
    plt.ylabel('赔付率(%)')
    plt.legend()
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(os.path.join(period_dir, 'loss_ratios_comparison.png'), dpi=300)
    plt.close()

    # 4. 综合资本成本率
    plt.figure(figsize=(12, 6))
    plt.fill_between(metrics[period_col], 0, metrics['comprehensive_capital_cost'],
                     color='salmon', alpha=0.3)
    plt.plot(metrics[period_col], metrics['comprehensive_capital_cost'], 'r-', linewidth=2)
    plt.title(f'综合资本成本率 ({period_type}趋势)')
    plt.ylabel('成本率(%)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(os.path.join(period_dir, 'comprehensive_capital_cost.png'), dpi=300)
    plt.close()

    # 5. 所有指标综合图表
    plt.figure(figsize=(14, 8))
    fig, ax1 = plt.subplots()

    # 左侧Y轴（退保率和赔付率）
    color = 'tab:blue'
    ax1.set_xlabel(period_type)
    ax1.set_ylabel('比率(%)', color=color)
    l1 = ax1.plot(metrics[period_col], metrics['long_term_surrender_rate'], 'o-', color=color, label='长期险退保率')
    l2 = ax1.plot(metrics[period_col], metrics['paid_loss_ratio'], 's-', color='green', label='已付赔付率')
    l3 = ax1.plot(metrics[period_col], metrics['total_loss_ratio'], 'd-', color='purple', label='总赔付率')
    ax1.tick_params(axis='y', labelcolor=color)

    # 右侧Y轴（准备金比例和资本成本）
    ax2 = ax1.twinx()
    color = 'tab:red'
    ax2.set_ylabel('比率(%)', color=color)
    l4 = ax2.plot(metrics[period_col], metrics['reserve_claim_ratio'], 'X-', color=color, label='准备金/赔款比')
    l5 = ax2.plot(metrics[period_col], metrics['comprehensive_capital_cost'], '*-', color='orange',
                  label='综合资本成本率')
    ax2.tick_params(axis='y', labelcolor=color)

    # 合并图例
    lines = l1 + l2 + l3 + l4 + l5
    labels = [l.get_label() for l in lines]
    plt.legend(lines, labels, loc='upper left', bbox_to_anchor=(0, -0.15), ncol=3)

    plt.title(f'保险核心指标综合分析 ({period_type}趋势)')
    plt.xticks(rotation=45)
    fig.tight_layout()
    plt.savefig(os.path.join(period_dir, 'all_metrics_comparison.png'), dpi=300)
    plt.close()


# 主函数
def main():
    # 大数据-八维保险数据挖掘-05-财产保险成本费用相关 (工单编号)
    print("八维保险数据挖掘项目 - 财产保险成本费用指标计算")
    print("工单编号: 大数据-八维保险数据挖掘-05-财产保险成本费用相关")

    # 创建输出目录
    output_dir = "insurance_metrics_visualization"
    os.makedirs(output_dir, exist_ok=True)

    # 生成模拟数据
    time_dim, policy, reserve, claim, capital = generate_sample_data()

    # 按不同时间维度计算指标
    weekly_metrics = calculate_metrics(time_dim, policy, reserve, claim, capital, 'week')
    monthly_metrics = calculate_metrics(time_dim, policy, reserve, claim, capital, 'month')
    yearly_metrics = calculate_metrics(time_dim, policy, reserve, claim, capital, 'year')

    # 导出结果到Excel
    with pd.ExcelWriter('insurance_cost_metrics.xlsx') as writer:
        weekly_metrics.to_excel(writer, sheet_name='Weekly_Metrics', index=False)
        monthly_metrics.to_excel(writer, sheet_name='Monthly_Metrics', index=False)
        yearly_metrics.to_excel(writer, sheet_name='Yearly_Metrics', index=False)

    # 可视化指标
    print("生成可视化图表...")
    visualize_metrics(weekly_metrics, 'weekly', output_dir)
    visualize_metrics(monthly_metrics, 'monthly', output_dir)
    visualize_metrics(yearly_metrics, 'yearly', output_dir)

    print(f"指标计算完成，结果已保存到 insurance_cost_metrics.xlsx")
    print(f"可视化图表已保存到 {output_dir} 文件夹")


if __name__ == "__main__":
    main()